#include "projectdatabasaoperations.h"
#include <QDebug>
#include "tabledefine.h"

QSqlDatabase projectDB;

bool connectProjectDB(QString dbName)
{
    if (QSqlDatabase::contains(PROJECTDB_CONNECTION))
    {
        projectDB = QSqlDatabase::database(PROJECTDB_CONNECTION);
    }
    else
    {
        projectDB = QSqlDatabase::addDatabase("QSQLITE", PROJECTDB_CONNECTION);
        projectDB.setDatabaseName(dbName);
        projectDB.setUserName("yanshang");
        projectDB.setPassword("912");
    }
    if(!projectDB.open())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<projectDB.lastError();
        return false;
    }
    return true;
}

void closeProjectDB()
{
    if(projectDB.isOpen())
        projectDB.close();
}

void removeProjectDB()
{
    if(QSqlDatabase::contains(PROJECTDB_CONNECTION))
        QSqlDatabase::removeDatabase(PROJECTDB_CONNECTION);
}

bool initProjectDB()
{   
    QSqlQuery query(projectDB);
    QStringList tables = projectDB.tables();
    
    //建筑表
    QString sql = QString("create table %1 (%2 int primary key, "
                  "%3 VARCHAR(200), "
                  "%4 VARCHAR(500),"
                  "%5 int, "
                  "%6 int, "
                  "%7 int)").arg(BUILDING_TABLE_NAME, BUILDING_ID,
                                 NAME, 
                                 DESCRIBE,
                                 NUM,
                                 UP_ID,
                                 TYPE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(BUILDING_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑表创建成功";
    }
    else
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑表格已存在";
    
    //设备表
    sql.clear();
    sql = QString("create table %1 (%2 int primary key, "
                  "%3 int, "
                  "%4 VARCHAR(100), "
                  "%5 VARCHAR(10), "
                  "%6 VARCHAR(100), "
                  "%7 int, "
                  "%8 int, "
                  "%9 int, "
                  "%10 int, "
                  "%11 int, "
                  "%12 VARCHAR(50), "
                  "%13 VARCHAR(50), "
                  "%14 VARCHAR(200), "
                  "%15 VARCHAR(20), "
                  "%16 VARCHAR(20), "
                  "%17 VARCHAR(20))").arg(DEVICE_TABLE_NAME).arg(DEVICE_ID)
                                        .arg(BUILDING_ID)
                                        .arg(NAME)
                                        .arg(DEVICE_PA)
                                        .arg(APP_PROGRAM)
                                        .arg(ADR)
                                        .arg(PRG)
                                        .arg(PAR)
                                        .arg(GRP)
                                        .arg(CFG)
                                        .arg(MANUFACTURERS)
                                        .arg(ORDER_NUM)
                                        .arg(PRODUCT, UNIT_TYPE)
                                        .arg(MANUFACTURERSDATA)
                                        .arg(VERSION);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(DEVICE_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备表创建完成";
    }
    else
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备表格已存在";
    
    //群组地址表
    sql.clear();
    sql = QString("create table %1 (%2 int primary key, "
                  "%3 int, "
                  "%4 VARCHAR(50), "
                  "%5 VARCHAR(200), "
                  "%6 int, "
                  "%7 VARCHAR(10), "
                  "%8 int)").arg(GROUP_ADDRESS_TABLE_NAME,GROUP_ADDRESS_ID,
                                 VALUE,
                                 NAME,
                                 DESCRIBE,
                                 UP_ID,
                                 ADDRESS,
                                 TYPE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(GROUP_ADDRESS_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"群组地址表创建完成";
    }
    else
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"群组地址表已存在";
    
    //关联表
    sql.clear();
    sql = QString("create table %1 (%2 int primary key, "
                  "%3 int, "
                  "%4 int, "
                  "%5 int)").arg(RELATION_TABLE_NAME, RELATION_ID,
                                 GROUP_ADDRESS_ID,
                                 DEVICE_ID,
                                 COMM_OBJECT_NUM);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(RELATION_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"关联表创建成功";
    }
    else
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"关联表已存在";
    
    return true;
}


QVariant getBySql(QString sql)
{
    QSqlQuery query(projectDB);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"从数据库获取失败："<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取成功";
        return query.value(0);
    }
    return -2;
}

int getBuildingID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(BUILDING_ID, BUILDING_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库建筑最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int getDeviceID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(DEVICE_ID, DEVICE_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库设备最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int getAddressID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(GROUP_ADDRESS_ID, GROUP_ADDRESS_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库地址最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int isValueExist(int value, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(VALUE, GROUP_ADDRESS_TABLE_NAME, TYPE);
    query.prepare(sql);
    query.addBindValue(type);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    while(query.next())
    {
        if(query.value(0).toInt() == value)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"value已存在："<<query.value(0).toInt()<<"type值："<<type;
            return 1;
        }
    }
    return 0;
}

bool addBuilding(int buildingID, QString buildingName, QString buildingDescribe, int num,
                      int upID, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2").arg(NAME, BUILDING_TABLE_NAME);
    if(!query.exec(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    while(query.next())
    {
        if(query.value(0).toString() == buildingName)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑名称已存在"<<query.value(0).toString();
            QString tmp = QString("(%1)").arg(buildingID - 1);
            buildingName.append(tmp);
        }
    }
    
    if(-1 == num)
        sql = QString("insert into %1 (%2, %3, %4, %5, %6) values (?, ?, ?, ?, ?)").arg(BUILDING_TABLE_NAME, 
                                                                          BUILDING_ID, NAME, DESCRIBE, UP_ID, TYPE);
    else
        sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?)").arg(BUILDING_TABLE_NAME,
                                                                      BUILDING_ID, NAME, DESCRIBE, NUM, UP_ID, TYPE);
    
    if(!query.prepare(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(buildingID);
    query.addBindValue(buildingName);
    query.addBindValue(buildingDescribe);
    if(-1 != num)
        query.addBindValue(num);
    query.addBindValue(upID);
    query.addBindValue(type);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入建筑成功";
    return true;
}

bool addDevice(int deviceID, int buildingID, QString name, QString PA, QString appProgram, int adr, int prg, 
               int par, int grp, int cfg, QString manufacturers, QString orderNum, 
               QString product, QString unitType, QString manufacturesData, QString version)
{
    QSqlQuery query(projectDB);
    QString sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)").arg(DEVICE_TABLE_NAME);
    
    if(!query.prepare(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(deviceID);
    query.addBindValue(buildingID);
    query.addBindValue(name);
    query.addBindValue(PA);
    query.addBindValue(appProgram);
    query.addBindValue(adr);
    query.addBindValue(prg);
    query.addBindValue(par);
    query.addBindValue(grp);
    query.addBindValue(cfg);
    query.addBindValue(manufacturers);
    query.addBindValue(orderNum);
    query.addBindValue(product);
    query.addBindValue(unitType);
    query.addBindValue(manufacturesData);
    query.addBindValue(version);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入设备成功";
    return true;
}

bool addAddress(int addressID, int value, QString name, QString describe, int upID, 
                       QString address, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?, ?)").arg(GROUP_ADDRESS_TABLE_NAME,
                                                                            GROUP_ADDRESS_ID, VALUE, NAME, DESCRIBE,
                                                                            UP_ID, ADDRESS, TYPE);
    
    if(!query.prepare(sql))
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(addressID);
    query.addBindValue(value);
    query.addBindValue(name);
    query.addBindValue(describe);
    query.addBindValue(upID);
    query.addBindValue(address);
    query.addBindValue(type);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入地址成功";
    return true;
}

bool deleteBuildingByID(int buildingID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(BUILDING_TABLE_NAME, BUILDING_ID);
    
    query.prepare(sql);
    query.addBindValue(buildingID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteBuildingByName(QString name)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(BUILDING_TABLE_NAME, NAME);
    
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteDeviceByID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(DEVICE_TABLE_NAME, DEVICE_ID);
    
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteAddressByID(int addressID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(GROUP_ADDRESS_TABLE_NAME, GROUP_ADDRESS_ID);
    
    query.prepare(sql);
    query.addBindValue(addressID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteAddressByAddress(QString address)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(GROUP_ADDRESS_TABLE_NAME, ADDRESS);
    
    query.prepare(sql);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"sql";
    query.addBindValue(address);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool updateBuildingName(int buildingID, QString buildingName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, buildingName);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑名称成功";
    return true;
}

QString getBuilgingNameByID(int buildingID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(NAME, BUILDING_TABLE_NAME, BUILDING_ID);
    query.prepare(sql);
    query.addBindValue(buildingID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"根据id获取建筑名成功"<<query.value(0).toString();
        return query.value(0).toString();
    }
    return NULL;
}

bool updateBuildingDescribe(int buildingID, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑描述成功";
    return true;
}

bool updateBuildingNum(int buildingID, int num)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     NUM, _NUM, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_NUM, num);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑序号成功";
    return true;
}

bool updateDeviceName(int deviceID, QString deviceName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(DEVICE_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     DEVICE_ID, _DEVICE_ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, deviceName);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新设备名称成功";
    return true;
}

bool updateDeviceDescribe(int deviceID, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(DEVICE_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     DEVICE_ID, _DEVICE_ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新设备描述成功";
    return true;
}

bool updateAddressName(int addressID, QString addressName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(GROUP_ADDRESS_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     GROUP_ADDRESS_ID, _GROUP_ADDRESS_ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, addressName);
    query.bindValue(_GROUP_ADDRESS_ID, addressID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新地址名称成功";
    return true;
}

bool updateAddressDescribe(int addressID, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(GROUP_ADDRESS_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     GROUP_ADDRESS_ID, _GROUP_ADDRESS_ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_GROUP_ADDRESS_ID, addressID);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新地址描述成功";
    return true;
}
